Text-to-SQL Datasource Plugin

Link to Plugin Source Code

Overview

A Godspeed plugin that enables natural language to SQL conversion and multi-database query execution with built-in caching support. This plugin leverages Gemini models to convert natural language queries into SQL statements and executes them across various database types.

Features

• Natural language to SQL conversion using Google Gemini
• Multi-database support
  • PostgreSQL
  • MySQL
  • MongoDB
  • Oracle
• Automatic query caching with Redis
• Query validation
• Schema management
• Error handling and logging
• Connection pooling
• Resource cleanup

1. Install the Plugin

First, add the Text-to-SQL plugin using

godspeed plugin add @godspeedsystems/plugins-text-to-sql-as-datasource      

Setup Environment

Create or update your .env file with the following:

# Gemini API Configuration
GEMINI_API_KEY=your_gemini_api_key

# PostgreSQL Configuration
PG_USER=your_postgres_user
PG_HOST=localhost
PG_DB=your_database
PG_PASSWORD=your_password
PG_PORT=5432

# MySQL Configuration
MYSQL_HOST=localhost
MYSQL_USER=your_mysql_user
MYSQL_PASSWORD=your_password
MYSQL_DB=your_database

# MongoDB Configuration
MONGODB_URL=mongodb://localhost:27017
MONGODB_DB=your_database

# Oracle Configuration
ORACLE_USER=your_oracle_user
ORACLE_PASSWORD=your_password
ORACLE_CONNECT_STRING=localhost:1521/XEPDB1

# Redis Configuration
REDIS_URL=redis://localhost:6379

DataSource Configuration

Check your auto generated YAML configuration file src/datasources/text-to-sql.yaml:

type: text-to-sql
config:
  gemini:
    apiKey: ${GEMINI_API_KEY}
  databases:
    postgres:
      enabled: true
      config:
        user: ${PG_USER}
        host: ${PG_HOST}
        database: ${PG_DB}
        password: ${PG_PASSWORD}
        port: ${PG_PORT}
  cache:
    enabled: true
    url: ${REDIS_URL}

Define the Event (API Endpoint)

Create src/events/sql-query.yaml:

http.post./sql-query:
  summary: "Convert natural language to SQL and execute"
  description: "Endpoint to convert natural language to SQL query and execute it"
  fn: execute-query
  authn: false
  body:
    content:
      application/json:
        schema:
          type: object
          properties:
            query:
              type: string
              description: "Natural language query to be converted to SQL"
            dbType:
              type: string
              description: "Target database type (postgres, mysql, etc.)"
              default: "postgres"
          required:
            - query
  responses:
    200:
      description: "Successful query execution"
      content:
        application/json:
          schema:
            type: object

3. Write Event Handler Function

Create src/functions/execute-query.ts:

import { GSContext, PlainObject } from "@godspeedsystems/core";

async function handler(ctx: GSContext): Promise<PlainObject> {
  try {
    const { body } = ctx.inputs.data;
    
    if (!body.query) {
      throw new Error("Query is required");
    }

    // Execute the query using the text-to-sql datasource
    const result = await ctx.datasources['text-to-sql'].execute(ctx, {
      query: body.query,
      dbType: body.dbType || 'postgres',
      validateOnly: false,
      cache: true
    });

    return {
      success: true,
      data: result
    };

  } catch (error: any) {
    ctx.logger.error('Query execution failed:', error);
    return {
      success: false,
      code: 500,
      message: error.message,
      data: {
        message: "Query execution failed"
      }
    };
  }
}
export default handler;

Database Setup

PostgreSQL Container Setup

# Run PostgreSQL container
sudo docker run --name test-postgres \
  -e POSTGRES_USER=testuser \
  -e POSTGRES_PASSWORD=testpass \
  -e POSTGRES_DB=testdb \
  -p 5434:5432 \
  -d postgres:latest

# Create test data
psql -h localhost -p 5434 -U testuser -d testdb

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    status VARCHAR(20)
);

INSERT INTO users (name, email, status) VALUES 
    ('John Doe', 'john@example.com', 'active'),
    ('Jane Smith', 'jane@example.com', 'inactive');

Redis Cache Setup

# Run Redis container
sudo docker run --name test-redis \
  -p 6380:6379 \
  -d redis:latest

Test API

POST /sql-query

Request Body

{
  "query": "find all active users",
  "dbType": "postgres"
}

Response

{
  "success": true,
  "data": {
    "rows": [
      {
        "id": 1,
        "name": "John Doe",
        "email": "john@example.com",
        "status": "active"
      }
    ],
    "metadata": {
      "rowCount": 1,
      "executionTime": "0.123s"
    }
  }
}

Key Benefits

1. Developer Productivity

   • No need to write SQL queries manually
   • Rapid prototyping and development
   • Focus on business logic instead of query syntax

2. End User Experience

   • Natural language interface to databases
   • Faster query results with caching
   • Reduced learning curve

3. Enterprise Ready

   • Production-grade security
   • Scalable architecture
   • Comprehensive logging
   • Performance optimization

Advanced Examples

Complex Queries

{
  "query": "Show me total sales by category for active products in last quarter",
  "dbType": "postgres"
}

Analytical Queries

{
  "query": "Calculate monthly growth rate of user signups",
  "dbType": "postgres"
}

Security Features

• Query validation before execution
• SQL injection prevention
• Rate limiting support
• Error handling and sanitization
• Secure database connections

Performance

• Redis caching reduces database load
• Query optimization suggestions
• Connection pooling
• Execution time monitoring
• Cache hit ratio tracking

Example Usage

Basic Query Event Configuration

Create a new file src/events/query.yaml:

'http.post./query':
  fn: execute-query
  body:
    content:
      application/json:
        schema:
          type: object
          properties:
            query:
              type: string
            dbType:
              type: string
              default: postgres
          required: ['query']

Query Execution Workflow

Define your function src/functions/execute-query.yaml:

id: execute-query
summary: Execute natural language query
tasks:
  - id: convert-and-execute
    fn: datasource.text-to-sql.execute
    args:
      query: <% inputs.body.query %>
      dbType: <% inputs.body.dbType || 'postgres' %>

Example Requests

1. Simple Query Request:

curl -X POST http://localhost:3000/query \
  -H "Content-Type: application/json" \
  -d '{"query": "Find all active users"}'

2. Complex Query Request:

curl -X POST http://localhost:3000/query \
  -H "Content-Type: application/json" \
  -d '{
    "query": "Show total sales by category for the last quarter",
    "dbType": "mysql"
  }'

Example Responses

{
  "sql": "SELECT category, SUM(amount) AS total_sales FROM sales WHERE date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)",
  "data": [
    {
      "category": "Electronics",
      "total_sales": 105000
    }
  ],
  "metadata": {
    "rowCount": 1,
    "executionTime": 48
  }
}

Error Handling

{
  "error": "QUERY_ERROR",
  "message": "Failed to execute query",
  "details": "Syntax error at or near 'total'"
}

Limitations

1. Natural language processing depends on Google Gemini
2. Redis required for caching functionality
3. Database-specific features may vary

Troubleshooting

Common Issues

1. Connection Failures

   • Verify database credentials
   • Check network connectivity
   • Ensure services are running

2. Query Generation Issues

   • Validate Google gemini API key
   • Check query format
   • Review database schema

3. Cache Problems

   • Verify Redis connection
   • Check cache configuration
   • Monitor memory usage